ALTER          proc sp_inventory_cost_report
-- Input parameter is process StepID;
@process_step_id int,
@process_step_type varchar(15),
@date_From varchar(10),
@date_To   varchar(10)


as

/*variable to store the values
*/
declare @qty_in int, @qty_out int, @yield float
declare @date_in datetime, @date_out datetime
declare @temp_lot_id int
declare @cost float


if @process_step_type='Mark and Pack'
begin
set @process_step_type = 'Mark & Pack'
end


declare @lotCursor CURSOR
declare flowCursor CURSOR FOR
select * from fn_get_process_flow_by_id(@process_step_id)

declare @@temp_process_step_id bigint
declare @@temp_ps_type varchar(20)
declare @@temp_ps_type_name varchar(20)
open flowCursor 
fetch next from flowCursor into @@temp_process_step_id,@@temp_ps_type,@@temp_ps_type_name

while @@fetch_status = 0
begin
-- Cursor to store the Lot_Number and Lot_ID
-- If process_step_id variable is 0 store all lot_numbers and 
set @lotCursor = CURSOR for
select Lot_ID, Lot_Number,ProcessStep_ID from Lot_Table where ProcessStep_ID = @@temp_process_step_id and (Lot_Current_Status = 'Running' or  Lot_Current_Status='Queue')

set @@temp_process_step_id =null


declare @lot_no varchar(50)
declare @lot_id int
declare @running_process_id int
declare @lot_process_id int
declare @running_lot_id int
declare @running_lot_no varchar(50)
declare @results Table(Lot_ID varchar(20),Location varchar(50),Product varchar(50),Process_Name varchar(50), Qty_in int, Qty_out int, Date_in datetime, Date_out datetime,Cost float)
open @lotCursor 
fetch next from @lotCursor
into @lot_id, @lot_no,@lot_process_id

while @@fetch_status = 0
begin
	
	print @lot_no
	print @lot_id
	print @lot_process_id

	declare @procstepCursor CURSOR
	select @running_process_id=ProcessStep_ID,@running_lot_id=Lot_ID,@running_lot_no=Lot_Number  from Lot_Table where ProcessStep_ID in (select ProcessStep_ID from fn_get_process_flow_by_id(@process_step_id)) and  Lot_Number = @lot_no and (Lot_Current_Status = 'Running' or  Lot_Current_Status='Queue')
	--select @running_process_id=ProcessStep_ID,@running_lot_id=Lot_ID,@running_lot_no=Lot_Number  from Lot_Table where Lot_Number = @lot_no and Lot_Current_Status = 'Running'

	--print '1 @running_process_id = '
	--print @running_process_id
	
	--print '1 @running_lot_no = '
	--print @running_lot_no



	set @procstepCursor = CURSOR for
	select fn_get_process_flow_backward_by_id.ProcessStep_ID,Lot_ID from fn_get_process_flow_backward_by_id(@running_process_id),Lot_Table where lot_table.lot_Number=@running_lot_no  and lot_table.ProcessStep_ID=fn_get_process_flow_backward_by_id.ProcessStep_ID order by temp_count desc	
		
	declare @type varchar(50)
	declare @testType varchar(50)
	declare @location varchar(50)
	declare @product varchar(50)
	declare @temp_step int
	declare @standardUnitCost float
        open @procstepCursor
	fetch next from @procstepCursor into @temp_step,@temp_lot_id
	while @@fetch_status = 0
	begin
		select @type = PS_Type_Name,@testType=PS_Type from Process_Step where ProcessStep_ID = @temp_step
		select @location = (select L_Vendor_Name from location,lot_table where lot_id=@temp_lot_id and lot_table.location_Id = location.location_Id)
		select @product =(select part_number from product where  processStep_id = @temp_step)
		
		print 'lot Number '+ convert(varchar(20),@lot_no )
		print 'lot Id '+convert(varchar(10),@lot_id )	


		print 'Type = '+@type
		if @type = 'Finished Goods' or @type ='Wafer Store' or @type = 'Class Store' or @type = 'Mark & Pack' or @type='Die Bank'
		begin
			declare @inv_Lot_Id int
			if (@date_From ='0' and @date_To = '0')
			begin
			select @qty_in = IL_Qty_In, @qty_out =IL_Qty_Out,  @date_in = IL_Date_In ,  @date_out = IL_Date_Out, @cost = Lot_Cost from Inventory_Lot, Lot_Table where Inventory_Lot.Lot_ID = @temp_lot_id and Lot_Table.Lot_ID = @temp_lot_id
			insert into @results(Lot_ID,Location,Product,Process_Name, Qty_in, Qty_out, Date_in, Date_out, Cost) values(@lot_no,@location,@product, @type, @qty_in, @qty_out, @date_in, @date_out, @cost) 
			end
			else if (@date_From <> '0' and @date_To <> '0')
			begin
			select @inv_Lot_Id = Inventory_Lot.Lot_ID, @qty_in = IL_Qty_In, @qty_out =IL_Qty_Out,  @date_in = IL_Date_In ,  @date_out = IL_Date_Out, @cost = Lot_Cost from Inventory_Lot, Lot_Table where Inventory_Lot.Lot_ID = @temp_lot_id and Inventory_Lot.IL_Date_Out>=@date_From and Inventory_Lot.IL_Date_In<=@date_To and Lot_Table.Lot_ID = @temp_lot_id
			if(@inv_Lot_Id<>'0')
			begin
			insert into @results(Lot_ID,Location,Product, Process_Name, Qty_in, Qty_out, Date_in, Date_out, Cost) values(@lot_no,@location,@product, @type, @qty_in, @qty_out, @date_in, @date_out, @cost) 
			end
			end
		end 	
		
 
		set @qty_in=null
		set @qty_out=null
		set @date_in=null
		set @date_out=null	
 		fetch next from @procstepCursor into @temp_step,@temp_lot_id
	end
	close @procstepCursor
	deallocate @procstepCursor
	set @running_lot_no = null
	set @running_process_id = 0
	set @type = null
	set @standardUnitCost=null	
	fetch next from @lotCursor
	into @lot_id, @lot_no,@lot_process_id
end
close @lotCursor
deallocate @lotCursor
fetch next from flowCursor into @@temp_process_step_id,@@temp_ps_type,@@temp_ps_type_name
end
close flowCursor
deallocate flowCursor

if (@date_From = '0' and @date_To = '0' and @process_step_type ='0' )
begin
select *from @results 
end
if (@date_From <> '0' and @date_To <> '0' and @process_step_type='0' )
begin
select *from @results where Date_In>=@date_From and Date_In<=@date_To
end
if (@date_From <> '0' and @date_To <> '0' and @process_step_type<>'0' )
begin
select *from @results where Date_In>=@date_From and Date_In<=@date_To and Process_Name=@process_step_type
end
if (@date_From = '0' and @date_To = '0' and @process_step_type<>'0' )
begin
select *from @results where Process_Name=@process_step_type
end

